package br.com.localfloripa.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.ExecutionException;

import javax.inject.Named;
import javax.persistence.Query;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Scope;

import br.com.localfloripa.dto.AuditoriaDto;
import br.com.localfloripa.entities.Auditoria;
import br.com.localfloripa.entities.TipoAuditoria;

@Named("auditoriaDao")
@Scope("singleton")
public class AuditoriaDao extends DaoManager<Auditoria> {
	
	private static final Logger log = LoggerFactory.getLogger(AuditoriaDao.class);
	
	
	public Auditoria findById(Long idAuditoria) throws Exception {
		return em.find(Auditoria.class, idAuditoria);
	}
	
	public List<AuditoriaDto> getAuditoriaGroupByTipo() throws Exception {
		
		List<AuditoriaDto> auditorias = new ArrayList<AuditoriaDto>();
		
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT a.tipo,year(a.data),COUNT(a.id) ");
		builder.append("FROM Auditoria a ");
		builder.append("GROUP BY a.tipo,year(a.data)");
		Query query = em.createQuery(builder.toString());
		
		Iterator it = query.getResultList().iterator();		
		while ( it.hasNext()  ) {
			Object[] row = (Object[]) it.next();
			TipoAuditoria tipo = (TipoAuditoria) row[0];
			Integer ano = (Integer) row[1];
			Long acessos = (Long) row[2];
			
			auditorias.add(new AuditoriaDto(tipo.value(),ano,acessos));						
		}				
		
		return auditorias;
		
	}
	
	public List<AuditoriaDto> getAuditoriaByIdUsuarioGroupByTipo(Long idUsuario) throws ExecutionException {
		
		List<AuditoriaDto> auditorias = new ArrayList<AuditoriaDto>();
		
		StringBuilder builder = new StringBuilder();				
		builder.append("SELECT a.tipo,year(a.data),COUNT(a.id) ");
		builder.append("FROM Auditoria e ");
		builder.append("JOIN FETCH e.anuncio a ");
		builder.append("JOIN FETCH a.cliente c ");
		builder.append("JOIN FETCH c.usuario u ");
		builder.append("WHERE u.id = :idUsuario ");
		builder.append("GROUP BY e.tipo,year(a.data)");
		Query query = em.createQuery(builder.toString());
		
		
		Iterator it = query.getResultList().iterator();		
		while ( it.hasNext()  ) {
			Object[] row = (Object[]) it.next();
			TipoAuditoria tipo = (TipoAuditoria) row[0];			
			Integer ano = (Integer) row[1];
			Long acessos = (Long) row[2];
			
			auditorias.add(new AuditoriaDto(tipo.value(),ano,acessos));	
		}				
		return auditorias;
		
	}

}
